﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Yu.Cms.Common;
using Yu.Cms.IDAL;

namespace Yu.Cms.SQLServerDAL
{
    public class Book : IBook
    {
        private readonly string TableName = "Y_Book";

        #region IBook 成员

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Yu.Cms.Model.Book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Y_Book(");
            strSql.Append("bName,bWeb,bEmail,bContent,PubTime,PubIp,isPub)");
            strSql.Append(" values (");
            strSql.Append("@bName,@bWeb,@bEmail,@bContent,@PubTime,@PubIp,@isPub)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@bName", SqlDbType.NVarChar,10),
					new SqlParameter("@bWeb", SqlDbType.NVarChar,100),				
					new SqlParameter("@bEmail", SqlDbType.NVarChar,50),
					new SqlParameter("@bContent", SqlDbType.NVarChar,1000),
					new SqlParameter("@PubTime", SqlDbType.SmallDateTime),				
					new SqlParameter("@PubIp", SqlDbType.VarChar,15),
                    new SqlParameter("@isPub", SqlDbType.Bit,4)};

            parameters[0].Value = model.bName;
            parameters[1].Value = model.bWeb;          
            parameters[2].Value = model.bEmail;          
            parameters[3].Value = model.bContent;
            parameters[4].Value = model.PubTime;           
            parameters[5].Value = model.PubIp;
            parameters[6].Value = model.isPub;

            object obj = DbHelperMsSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(Yu.Cms.Model.Book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + TableName + " set ");
            strSql.Append("bName=@bName,");
            strSql.Append("bWeb=@bWeb,");           
            strSql.Append("bEmail=@bEmail,");
            strSql.Append("bContent=@bContent,");
            strSql.Append("bReContent=@bReContent,");
            strSql.Append("RePubTitme=@RePubTitme,");
            strSql.Append("isPub=@isPub ");
            strSql.Append(" where bId=@bId ");
            SqlParameter[] parameters = {			
	                new SqlParameter("@bName", SqlDbType.NVarChar,10),
					new SqlParameter("@bWeb", SqlDbType.NVarChar,100),				
					new SqlParameter("@bEmail", SqlDbType.NVarChar,50),
					new SqlParameter("@bContent", SqlDbType.NVarChar,1000),
					new SqlParameter("@bReContent", SqlDbType.NVarChar,1000),
					new SqlParameter("@RePubTitme", SqlDbType.SmallDateTime),
                    new SqlParameter("@isPub", SqlDbType.Bit),
                     new SqlParameter("@bId", SqlDbType.Int)
					};
            parameters[0].Value = model.bName;
            parameters[1].Value = model.bWeb;           
            parameters[2].Value = model.bEmail;
            parameters[3].Value = model.bContent;
            parameters[4].Value = model.bReContent;
            parameters[5].Value = model.RePubTitme;
            parameters[6].Value = model.isPub;
            parameters[7].Value = model.bId;
            
            return DbHelperMsSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="bId"></param>
        /// <returns></returns>
        public int Delete(int bId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + TableName);
            strSql.Append(" where bId=@bId ");
            SqlParameter[] parameters = {
					new SqlParameter("@bId", SqlDbType.Int,4)};
            parameters[0].Value = bId;

            return DbHelperMsSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="bId"></param>
        /// <returns></returns>
        public int DeleteAll(string bId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + TableName);
            strSql.AppendFormat(" where bId in ({0}) ", bId);
            return DbHelperMsSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 批量通过审核
        /// </summary>
        /// <param name="bId">批量id，格式 1,2,3,4,5</param>
        /// <param name="isBool">true:通过审核 false:取消审核</param>
        /// <returns></returns>
        public int UpdateIsPub(string bId, bool isBool)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update "+TableName);
            if (isBool)
            {
                strSql.Append(" set ispub=1");
            }
            else
            {
                strSql.Append(" set ispub=0");
            }
            strSql.Append(" where bId in ("+bId+")");
            return DbHelperMsSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="bId"></param>
        /// <returns></returns>
        public Yu.Cms.Model.Book GetModel(int bId)
        {
            QueryParam qp = new QueryParam();
            qp.Where = " Where bId=" + bId;
            Yu.Cms.Model.Book fam = new Yu.Cms.Model.Book();
            int RecordCount;
            IList<Yu.Cms.Model.Book> lst = this.GetList(qp, out RecordCount);
            if (lst.Count > 0)
            {
                fam = lst[0];
            }
            return fam;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="CurrentPageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public IList<Yu.Cms.Model.Book> GetPubList(int CurrentPageIndex, int PageSize, out int RecordCount)
        {
            QueryParam qp = new QueryParam();
            qp.PageIndex = CurrentPageIndex;
            qp.PageSize = PageSize;
            qp.Where = " Where isPub=1"; //审核通过的
            return this.GetList(qp, out RecordCount);
        }


          /// <summary>
        /// 获得所有留言列表
        /// </summary>
        /// <param name="CurrentPageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public IList<Yu.Cms.Model.Book> GetAllList(int CurrentPageIndex, int PageSize, out int RecordCount)
        {
            QueryParam qp = new QueryParam();
            qp.PageIndex = CurrentPageIndex;
            qp.PageSize = PageSize;
            return this.GetList(qp, out RecordCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="iTop"></param>
        /// <returns></returns>
        public IList<Yu.Cms.Model.Book> GetNewList(int iTop)
        {
            QueryParam qp = new QueryParam();
            qp.PageIndex = 1;
            qp.PageSize = iTop;
            qp.OrderType = 1;
            qp.Orderfld = "bId";
            qp.Where = " Where isPub=1"; //审核通过的
            int RecordCount;
            return this.GetList(qp, out RecordCount);
        }

        #endregion

        /// <summary>
        /// 将记录集转为Y_Book实体类 (Y_Book)
        /// </summary>
        /// <param name="dr">记录集</param>
        /// <param name="Fileds">字段名列表</param>
        /// <returns>Y_Book</returns>
        protected Yu.Cms.Model.Book Populate_Book(IDataReader dr, Dictionary<string, string> Fileds)
        {
            Yu.Cms.Model.Book nc = new Yu.Cms.Model.Book();

            if (Fileds.ContainsKey("bId") && !Convert.IsDBNull(dr["bId"])) nc.bId = Convert.ToInt32(dr["bId"]); // 自动ID
            if (Fileds.ContainsKey("bName") && !Convert.IsDBNull(dr["bName"])) nc.bName = Convert.ToString(dr["bName"]).Trim(); // 留言姓名
            if (Fileds.ContainsKey("bWeb") && !Convert.IsDBNull(dr["bWeb"])) nc.bWeb = Convert.ToString(dr["bWeb"]).Trim(); // 留言人URL
            //if (Fileds.ContainsKey("bQq") && !Convert.IsDBNull(dr["bQq"])) nc.bQq = Convert.ToInt32(dr["bQq"]); // 留言人QQ
            if (Fileds.ContainsKey("bEmail") && !Convert.IsDBNull(dr["bEmail"])) nc.bEmail = Convert.ToString(dr["bEmail"]).Trim(); // 留言人EMAIL
            if (Fileds.ContainsKey("bContent") && !Convert.IsDBNull(dr["bContent"])) nc.bContent = Convert.ToString(dr["bContent"]).Trim(); // 留言内容
            if (Fileds.ContainsKey("PubTime") && !Convert.IsDBNull(dr["PubTime"])) nc.PubTime = Convert.ToDateTime(dr["PubTime"]); // 创建时间
            if (Fileds.ContainsKey("bReContent") && !Convert.IsDBNull(dr["bReContent"])) nc.bReContent = Convert.ToString(dr["bReContent"]).Trim(); // 回复内容
            if (Fileds.ContainsKey("RePubTitme") && !Convert.IsDBNull(dr["RePubTitme"])) nc.RePubTitme = Convert.ToDateTime(dr["RePubTitme"]); // 回复时间
            if (Fileds.ContainsKey("PubIp") && !Convert.IsDBNull(dr["PubIp"])) nc.PubIp = Convert.ToString(dr["PubIp"]).Trim(); // 留言人IP
            if (Fileds.ContainsKey("isPub") && !Convert.IsDBNull(dr["isPub"])) nc.isPub = Convert.ToBoolean(dr["isPub"]); // 是否通过审核 
            return nc;
        }

        /// <summary>
        /// 返回Book实体类的IList对象 (Y_Book)
        /// </summary>
        /// <param name="qp">查询类(非安全函数,传入参数请进行Sql字符串过滤)</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>Book实体类的IList对象(Y_Book)</returns>
        protected IList<Yu.Cms.Model.Book> GetList(QueryParam qp, out int RecordCount)
        {
            qp.TableName = TableName;
            if (qp.Orderfld == null)
            {
                qp.Orderfld = "bId";
            }
            else if (qp.Orderfld != "bId")
            {
                qp.Orderfld += ",bId";
            }

            if (qp.ReturnFields == null)
            {
                qp.ReturnFields = "*";
            }
            else
            {
                qp.ReturnFields += ",";
                qp.ReturnFields += qp.Orderfld;
            }
            DbHelperMsSQL.PopulateDelegate<Yu.Cms.Model.Book> mypd = new DbHelperMsSQL.PopulateDelegate<Yu.Cms.Model.Book>(this.Populate_Book);
            return DbHelperMsSQL.GetRecordListBySql(mypd, qp, out RecordCount);
        }
    }
}
